package com.gis.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.gis.bean.Lost;
import com.gis.dto.LostDto;
import com.gis.util.JDBCUtils;
import com.gis.util.PageResult;

import cn.hutool.core.util.ArrayUtil;
import cn.hutool.core.util.StrUtil;

public class LostDao {

  QueryRunner qr = new QueryRunner(JDBCUtils.getC3p0DateSource());
  private static final LostDao INSTANCE = new LostDao();

  private LostDao() {
  }

  public static LostDao getInstance() {
    return INSTANCE;
  }

  /**
   * 新增Lost
   *
   * @param lost
   * @return
   */
  public int add(Lost lost) {
    int i = 0;
    try {
      i = qr.execute(
          "insert into tb_lost(username, goods_type_id, lost_time, lng, lat, address, description, file_name) values(?,?,?,?,?,?,?,?)",
          lost.getUsername(), lost.getGoodsTypeId(), lost.getLostTime(), lost.getLng(),
          lost.getLat(),
          lost.getAddress(), lost.getDescription(), lost.getFileName());
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
    return i;
  }

  public List<LostDto> listAll(Map<String, Object> params) {
    List<LostDto> lostList = new ArrayList<>();
    String sql = "select tb_lost.id, username, lng, lat,address, description, lost_time as lostTime, file_name as fileName, tb_goods_type.`name` as goodsType from tb_lost, tb_goods_type where tb_lost.goods_type_id = tb_goods_type.id";
    // 参数值，即SQL中的问号对应的值
    List<Object> objList = new ArrayList<Object>();
    for (String key : params.keySet()) {
      if (key.equals("goodsTypeId") && params.get(key) != null) {
        sql += " and goods_type_id = ?";
        objList.add(params.get(key));
      } else if (key.equals("startDate") && params.get(key) != null) {
        sql += " and lost_time > ?";
        objList.add(params.get(key));
      } else if (key.equals("endDate") && params.get(key) != null) {
        sql += " and lost_time < ?";
        objList.add(params.get(key));
      } else if(key.equals("username") && params.get(key) != null) {
        if(!params.get(key).equals("admin")) {
          sql += " and username = ?";
          objList.add(params.get(key));
        }
      }
    }
    try {
      lostList = qr.query(sql, ArrayUtil.toArray(objList, Object.class),new BeanListHandler<>(LostDto.class));
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return lostList;
  }

  /**
   * 条件分页
   *
   * @param params
   * @return
   */
  @SuppressWarnings("deprecation")
  public PageResult<LostDto> page(Map<String, Object> params) {
    String sql = "select tb_lost.id, username, lng, lat,address, description, lost_time as lostTime, file_name as fileName, tb_goods_type.`name` as goodsType from tb_lost, tb_goods_type where tb_lost.goods_type_id = tb_goods_type.id";
    String totalSql = "select count(1) from tb_lost where 1=1";
    // 参数值，即SQL中的问号对应的值
    List<Object> objList = new ArrayList<Object>();
    for (String key : params.keySet()) {
      if (key.equals("goodsTypeId") && params.get(key) != null) {
        sql += " and goods_type_id = ?";
        totalSql += " and goods_type_id = ?";
        objList.add(params.get(key));
      } else if (key.equals("startDate") && params.get(key) != null) {
        sql += " and lost_time > ?";
        totalSql += " and lost_time > ?";
        objList.add(params.get(key));
      } else if (key.equals("endDate") && params.get(key) != null) {
        sql += " and lost_time < ?";
        totalSql += " and lost_time < ?";
        objList.add(params.get(key));
      } else if(key.equals("username") && params.get(key) != null) {
        if(!params.get(key).equals("admin")) {
          sql += " and username = ?";
          totalSql += " and username = ?";
          objList.add(params.get(key));
        }
      }
    }

    Long total = 0L;
    try {
      total = qr
          .query(totalSql, ArrayUtil.toArray(objList, Object.class), new ScalarHandler<Long>(1));
    } catch (SQLException e1) {
      // TODO Auto-generated catch block
      e1.printStackTrace();
    }

    objList.add(params.get("offset"));
    objList.add(params.get("rows"));
    sql += " order by lost_time desc limit ?, ?";

    List<LostDto> lostList = new ArrayList<LostDto>();

    try {
      lostList = qr.query(sql, ArrayUtil.toArray(objList, Object.class),
          new BeanListHandler<LostDto>(LostDto.class));

    } catch (SQLException e) {
      e.printStackTrace();
    }
    PageResult<LostDto> pageResult = new PageResult<LostDto>();
    pageResult.setRows(lostList);
    pageResult.setTotal(total);
    return pageResult;
  }

  /**
   * 分页查询 寻物启事
   *
   * @param pageSize
   * @param pageNum
   * @return
   */
  public List<LostDto> page(int pageSize, int pageNum) {
    List<LostDto> lostList = new ArrayList<LostDto>();
    String sql = "select tb_lost.id, username, lng, lat,address, description, lost_time as lostTime, file_name as fileName, tb_goods_type.`name` as goodsType from tb_lost, tb_goods_type where tb_lost.goods_type_id = tb_goods_type.id limit ?, ?";
    try {
      lostList = qr
          .query(sql, new BeanListHandler<LostDto>(LostDto.class), (pageNum - 1) * pageSize,
              pageSize);
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return lostList;
  }

  /**
   * 查询总数
   *
   * @return
   */
  public Long total() {
    Long count = 0L;
    try {
      count = qr.query("select count(1) from tb_lost", new ScalarHandler<Long>(1));
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
    return count;
  }

  /**
   * 获取失物信息的详细内容
   *
   * @param id
   * @return
   */
  public String getDetail(Integer id) {
    String sql = "select description from tb_lost where id = ?";
    Map<String, Object> map = new HashMap<>();
    try {
      map = qr.query(sql, new MapHandler(), id);
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return (String) map.get("description");
  }

  /**
   * delete lost info by id
   *
   * @param id
   * @return
   */
  public int deleteLost(Integer id) {
    String sql = "delete from tb_lost where id = ?";
    int i = 0;
    try {
      i = qr.update(sql, id);
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return i;
  }

  /**
   * get lost by id
   *
   * @param id
   * @return
   */
  public Lost getLost(Integer id) {
    String sql = "select username, goods_type_id as goodsTypeId, lost_time as lostTime, lng, lat, address, description, file_name as fileName from tb_lost where id = ?";
    Lost lost = null;
    try {
      lost = qr.query(sql, new BeanHandler<Lost>(Lost.class), id);
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return lost;
  }

  /**
   * update lost by id
   *
   * @param lost
   * @return
   */
  public int updateLost(Lost lost) {

    String sql = "";
    int i = 0;
    try {
      if (StrUtil.isEmpty(lost.getFileName())) {
        sql = "update tb_lost set goods_type_id=?, lost_time=?, lng=?, lat=?, address=?, description=? where id = ?";
        i = qr.update(sql, lost.getGoodsTypeId(), lost.getLostTime(), lost.getLng(), lost.getLat(),
            lost.getAddress(), lost.getDescription(), lost.getId());
      } else {
        sql = "update tb_lost set goods_type_id=?, lost_time=?, lng=?, lat=?, address=?, description=?, file_name=? where id = ?";
        i = qr.update(sql, lost.getGoodsTypeId(), lost.getLostTime(), lost.getLng(), lost.getLat(),
            lost.getAddress(), lost.getDescription(), lost.getFileName(), lost.getId());
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return i;
  }
}
